#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
注释说明
"""

__author__ = 'hubert'

import os
import pymysql
from datetime import datetime


# 连接数据库
def conn_db():
    host = '192.168.4.73'
    user = 'bigdata'
    password = 'bigdata'
    port = 3306
    db_name = 'movieinfo'

    try:
        # 建立连接
        conn = pymysql.connect(host=host, user=user, password=password, db=db_name, port=port, charset="utf8")
    except Exception as ex:
        print("数据库链接异常", str(ex))
    # 游标
    cursor = conn.cursor()
    # 返回游标
    return cursor, conn


# 查询数据库
def select_db(subject_id):
    cursor, conn = conn_db()
    sql = "select id from douban_movie where subject_id = " + str(subject_id)

    try:
        # 执行sql语句
        print("SQL="+sql)
        cursor.execute(sql)
        result = cursor.fetchone()
        return result
    except Exception as ex_select:
        print("查询数据库异常", str(ex_select))
        return -1

    close_db(cursor, conn)

# 写入数据
def insert_db(csv_data):
    cursor, conn = conn_db()
    # print(csv_data)
    """
        SQL 插入语句
        name 0, poster_url 1, director 2, screenwriter 3, actor 4. type 5, area 6, language 7, releaseyear 8, duration 9, alias 10 rate 11, description 12, subject_id 13
    """
    sql = "INSERT INTO douban_movie (name, alias, director, actor, screenwriter, " \
          "releaseyear, area, type, duration, rate, description, poster_url, language, subject_id, " \
          "create_time, update_time) VALUES (" + "\"" + csv_data[0] + "\"" \
                                                                      "," + "\"" + csv_data[10] + "\"" + "," + "\"" + csv_data[2] + "\"" + "," + "\"" + csv_data[4] + "\"" + "," + "\"" + csv_data[3] + "\"" + \
          "," + "\"" + csv_data[8] + "\"" + "," + "\"" + csv_data[6] + "\"" + "," + "\"" + csv_data[5] + "\"" + "," + "\"" + csv_data[9] + "\"" + "," + \
          "\"" + csv_data[11] + "\"" + "," + "\"" + csv_data[12] + "\"" + "," + "\"" + csv_data[1] + "\"" + "," + "\"" + csv_data[7] + "\"" + "," + \
          csv_data[13] + "," + "\"" + datetime.now().strftime("%Y-%m-%d %H:%M:%S") + "\"" + "," + "\"" + datetime.now().strftime("%Y-%m-%d %H:%M:%S") + "\"" + ")"
    try:
        # 执行sql语句
        print("SQL="+sql)
        cursor.execute(sql)
        # 提交到数据库执行
        conn.commit()
    except Exception as ex_insert:
        # 如果发生错误则回滚
        conn.rollback()
        print("插入数据库异常", str(ex_insert))

    close_db(cursor, conn)


# 关闭数据库
def close_db(cursor, conn):
    print("关闭数据库链接")
    cursor.close()
    conn.close()


if __name__ == '__main__':
    # result = select_db(30201133)  # (45,)
    result = select_db(1)  # (45,)
    print(result)
    # print(len(result)) # 无数据时返回None
